/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package cult.ticketing.dao;

import cult.ticketing.bean.PurchaseRecord;
import cult.ticketing.bean.ScheduleItem;
import cult.ticketing.jdbc.DB;
import java.util.ArrayList;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.naming.NamingException;
import javax.sql.DataSource;
/**
 *
 * @author yo
 */
public class PurchaseRecordDAO {

    private final int NON_MEMBER_ID = -1;
    private final int NO_REFRUND_REQUEST = -1;
    
    public boolean addPurchaseRecord(int memberId, String email, int scheduleItemId, String seatIndex, String purchaseTime, int refundStatus) {
        boolean isSuccessful = true;
        String[] seatArr = seatIndex.split(";");

        try {
            DataSource ds = DB.getDataSource();
            Connection con = ds.getConnection();
            PreparedStatement pstmt = con.prepareStatement("INSERT INTO purchase_record([member_id], [email], [scheduleItem_id], [seat_index], [refund_status], [purchase_time])VALUES(?, ?, ?, ?, ?, ?);");
            pstmt.setInt(1, memberId);
            pstmt.setString(2, email);
            pstmt.setInt(3, scheduleItemId);
            pstmt.setInt(5, refundStatus);
            pstmt.setString(6, purchaseTime);

            for (int i=0; i<seatArr.length; i++) {
                 pstmt.setString(4, seatArr[i]);
                 int r = pstmt.executeUpdate();
                 if (r <= 0) {
                    isSuccessful = false;
                    break;
                }
            }

            if (pstmt != null) {
                pstmt.close();
            }
            if (con != null) {
                con.close();
            }
        } catch (NamingException ne) {
            /* ignore */
        } catch (SQLException sqle) {
            /* ignore */
        }

        return isSuccessful;
    }


    public ArrayList<PurchaseRecord> getPurchaseRecordByRefundStatus(int refundStatus) {
        ArrayList<PurchaseRecord> purchaseRecords = new ArrayList<PurchaseRecord>();
        //id	member_id	email	scheduleItem_id     seat_index	refund_status	purchase_time
        try {
            DataSource ds = DB.getDataSource();
            Connection con = ds.getConnection();
            PreparedStatement pstmt = con.prepareStatement("SELECT * FROM [purchase_record] where [refund_status] = ?;");
            pstmt.setInt(1, refundStatus);
            ResultSet rs = pstmt.executeQuery();
            while (rs != null && rs.next() != false) {
                PurchaseRecord purchaseRecord = new PurchaseRecord();
                purchaseRecord.setId(rs.getInt(1));
                purchaseRecord.setMemberId(rs.getInt(2));
                purchaseRecord.setEmail(rs.getString(3));
                purchaseRecord.setScheduleItemId(rs.getInt(4));
                purchaseRecord.setSeatIndex(rs.getString(5));
                purchaseRecord.setRefundStatus(rs.getInt(6));
                purchaseRecord.setPurchaseTime(rs.getString(7));

                purchaseRecords.add(purchaseRecord);
            }

            if (rs != null)
                rs.close();
            if (pstmt != null)
                pstmt.close();
            if (con != null)
                con.close();
        } catch (javax.naming.NamingException ne) {
            /* ignore */
        } catch (SQLException sqle) {
            /* ignore */
        }

        return purchaseRecords;
    }

    public ArrayList<PurchaseRecord> getPurchaseRecordByMemberId(int memberId) {
        ArrayList<PurchaseRecord> purchaseRecords = new ArrayList<PurchaseRecord>();
        //id	member_id	email	scheduleItem_id	seat_index	refund_status	purchase_time
        try {
            DataSource ds = DB.getDataSource();
            Connection con = ds.getConnection();
            PreparedStatement pstmt = con.prepareStatement("SELECT * FROM [purchase_record] where [member_id] = ?;");
            pstmt.setInt(1, memberId);
            ResultSet rs = pstmt.executeQuery();
            while (rs != null && rs.next() != false) {
                PurchaseRecord purchaseRecord = new PurchaseRecord();
                purchaseRecord.setId(rs.getInt(1));
                purchaseRecord.setMemberId(rs.getInt(2));
                purchaseRecord.setEmail(rs.getString(3));
                purchaseRecord.setScheduleItemId(rs.getInt(4));
                purchaseRecord.setSeatIndex(rs.getString(5));
                purchaseRecord.setRefundStatus(rs.getInt(6));
                purchaseRecord.setPurchaseTime(rs.getString(7));

                purchaseRecords.add(purchaseRecord);
            }

            if (rs != null)
                rs.close();
            if (pstmt != null)
                pstmt.close();
            if (con != null)
                con.close();
        } catch (javax.naming.NamingException ne) {
            /* ignore */
        } catch (SQLException sqle) {
            /* ignore */
        }

        return purchaseRecords;
    }

    public ArrayList<PurchaseRecord> getPurchaseRecordByMemberIdAndRefundStatus(int memberId, String mode) {


        ArrayList<PurchaseRecord> purchaseRecords = new ArrayList<PurchaseRecord>();
        //id	member_id	email	scheduleItem_id	seat_index	refund_status	purchase_time
        try {
            DataSource ds = DB.getDataSource();
            Connection con = ds.getConnection();
            String stmt = "";
            if (mode.equalsIgnoreCase("customerQuery"))
                stmt = "SELECT * FROM [purchase_record] where [member_id] = ? AND refund_status in (0, 1, 2);";
            else
                stmt = "SELECT * FROM [purchase_record] where refund_status = 0;";

            PreparedStatement pstmt = con.prepareStatement(stmt);
            if (mode.equalsIgnoreCase("customerQuery"))
                pstmt.setInt(1, memberId);
            ResultSet rs = pstmt.executeQuery();
            while (rs != null && rs.next() != false) {
                PurchaseRecord purchaseRecord = new PurchaseRecord();
                purchaseRecord.setId(rs.getInt(1));
                purchaseRecord.setMemberId(rs.getInt(2));
                purchaseRecord.setEmail(rs.getString(3));
                purchaseRecord.setScheduleItemId(rs.getInt(4));
                purchaseRecord.setSeatIndex(rs.getString(5));
                purchaseRecord.setRefundStatus(rs.getInt(6));
                purchaseRecord.setPurchaseTime(rs.getString(7));

                purchaseRecords.add(purchaseRecord);
            }

            if (rs != null)
                rs.close();
            if (pstmt != null)
                pstmt.close();
            if (con != null)
                con.close();
        } catch (javax.naming.NamingException ne) {
            /* ignore */
        } catch (SQLException sqle) {
            /* ignore */
        }

        return purchaseRecords;
    }

    public ArrayList<PurchaseRecord> getPurchaseRecordForRefundRequest(int memberId) {
        ArrayList<PurchaseRecord> purchaseRecords = new ArrayList<PurchaseRecord>();
        //id	member_id	email	scheduleItem_id	seat_index	refund_status	purchase_time
        try {
            DataSource ds = DB.getDataSource();
            Connection con = ds.getConnection();
            String query = "SELECT purchase_record.* from purchase_record, schedule_item where purchase_record.member_id=? AND purchase_record.refund_status = -1 AND purchase_record.scheduleItem_id=schedule_item.id AND DATEDIFF(hour, GETDATE(), schedule_item.show_time)>3;";
            PreparedStatement pstmt = con.prepareStatement(query);
            pstmt.setInt(1, memberId);
            ResultSet rs = pstmt.executeQuery();
            while (rs != null && rs.next() != false) {
                PurchaseRecord purchaseRecord = new PurchaseRecord();
                purchaseRecord.setId(rs.getInt(1));
                purchaseRecord.setMemberId(rs.getInt(2));
                purchaseRecord.setEmail(rs.getString(3));
                purchaseRecord.setScheduleItemId(rs.getInt(4));
                purchaseRecord.setSeatIndex(rs.getString(5));
                purchaseRecord.setRefundStatus(rs.getInt(6));
                purchaseRecord.setPurchaseTime(rs.getString(7));

                purchaseRecords.add(purchaseRecord);
            }

            if (rs != null)
                rs.close();
            if (pstmt != null)
                pstmt.close();
            if (con != null)
                con.close();
        } catch (javax.naming.NamingException ne) {
            /* ignore */
        } catch (SQLException sqle) {
            /* ignore */
        }

        return purchaseRecords;
    }

    public boolean updateRefundStatusById(int purchaseRecordId, int refundStatus) {
        boolean ifSuccessful = false;

        try {
            DataSource ds = DB.getDataSource();
            Connection conn = ds.getConnection();
            PreparedStatement pstmt = conn.prepareStatement("UPDATE [purchase_record] SET [refund_status] = ? WHERE [id] = ?;");
            pstmt.setInt(1, refundStatus);
            pstmt.setInt(2, purchaseRecordId);

            if (pstmt.executeUpdate() > 0) {
                ifSuccessful = true;
            }
            if (pstmt != null) {
                pstmt.close();
            }
            if (conn != null) {
                conn.close();
            }
        } catch (NamingException ne) {
            /* ignore */
        } catch (SQLException sqle) {
            /* ignore */
        }

        return ifSuccessful;
    }

    public PurchaseRecord getPurchaseRecordById(int scheduleItemId) {
        PurchaseRecord purchaseRecord = new PurchaseRecord();
        //id	member_id	email	scheduleItem_id	seat_index	refund_status	purchase_time
        try {
            DataSource ds = DB.getDataSource();
            Connection con = ds.getConnection();
            PreparedStatement pstmt = con.prepareStatement("SELECT * FROM [purchase_record] where [id] = ?;");
            pstmt.setInt(1, scheduleItemId);
            ResultSet rs = pstmt.executeQuery();
            while (rs != null && rs.next() != false) {
                purchaseRecord.setId(rs.getInt(1));
                purchaseRecord.setMemberId(rs.getInt(2));
                purchaseRecord.setEmail(rs.getString(3));
                purchaseRecord.setScheduleItemId(rs.getInt(4));
                purchaseRecord.setSeatIndex(rs.getString(5));
                purchaseRecord.setRefundStatus(rs.getInt(6));
                purchaseRecord.setPurchaseTime(rs.getString(7));
            }

            if (rs != null)
                rs.close();
            if (pstmt != null)
                pstmt.close();
            if (con != null)
                con.close();
        } catch (javax.naming.NamingException ne) {
            /* ignore */
        } catch (SQLException sqle) {
            /* ignore */
        }

        return purchaseRecord;
    }
}
